package com.zb.frame.base.upgrade.common;

import com.zb.frame.base.upgrade.enums.DBType;
import com.zb.frame.base.upgrade.enums.ValueType;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.Connection;
import java.sql.Statement;
import java.util.*;

public class UpgradeHelper {

    private static Logger logger = LoggerFactory.getLogger(UpgradeHelper.class);

    private static Map<DBType, Map<ValueType, String>> columnTypeMapCache;

    // 创建表_SQL语句
    public static String getCreateSQL(DBType dbType, String tableName, String[] columnNames, ValueType[] types, boolean[] nullable, String[] primaryKeys, Integer[] fieldLens) {
        return getCreateSQL(dbType, null, tableName, columnNames, types, nullable, primaryKeys, fieldLens, null);
    }

    // 创建表_SQL语句
    public static String getCreateSQL(DBType dbType, String tableName, String[] columnNames, ValueType[] types, boolean[] nullable, String[] primaryKeys, Integer[] fieldLens, String[] identityKeys) {
        return getCreateSQL(dbType, null, tableName, columnNames, types, nullable, primaryKeys, fieldLens, identityKeys);
    }

    /**
     * 创建表_SQL语句,标准
     * @param dbType		数据库类型
     * @param schema		schema名
     * @param tableName		表名
     * @param columnNames	字段名数组
     * @param types			字段类型数组
     * @param nullable		可为空数组,true可空/false非空
     * @param primaryKeys	主键数组
     * @param fieldLens	    字段长度数组
     * @param identityKeys	需自增字段名数组，只支持MYSQL和SQLServer
     * @return
     */
    public static String getCreateSQL(DBType dbType, String schema, String tableName, String[] columnNames, ValueType[] types, boolean[] nullable, String[] primaryKeys, Integer[] fieldLens, String[] identityKeys) {
        // 处理自增字段映射
        Map<String,String> identityMap = new HashMap<String,String>();
        if (identityKeys != null && identityKeys.length > 0){
            for (String str : identityKeys) {
                if (dbType == DBType.SQLSERVER) identityMap.put(str, " identity(1,1)");
                else if(dbType == DBType.MYSQL) identityMap.put(str, " auto_increment");
            }
        }
        // 创建SQL,处理有schema的情况
        StringBuilder sb = new StringBuilder();
        if (StringUtils.isEmpty(schema)) {
            sb.append("create table ").append(tableName.toLowerCase()).append('(');
        } else {
            sb.append("create table ").append(schema).append(".").append(tableName.toLowerCase()).append('(');
        }
        // 添加字段信息
        for(int i= 0, len = columnNames.length; i < len; i++) {
            // 字段类型+长度
            String column = columnNames[i].toLowerCase();
            sb.append(column).append(' ').append(getColumnTypeSQL(dbType, types[i], fieldLens[i]));
            // 是否自增
            if(identityMap.containsKey(column))
                sb.append(identityMap.get(column));
            // 是否为空
            sb.append(' ').append(getColumnNullableSQL(nullable[i])).append(",");
        }
        // 添加 主键
        if (primaryKeys != null && primaryKeys.length != 0) {
            sb.append("primary key(");
            for(int i= 0; i < primaryKeys.length; i++)
                sb.append(primaryKeys[i]).append(',');
            sb.setLength(sb.length() - 1);
            sb.append(')');
        } else {
            sb.setLength(sb.length() - 1);
        }
        sb.append(')');
        switch (dbType) {
            case MYSQL:
                sb.append(" ENGINE = InnoDB ");
                break;
            default:
                break;
        }
        logger.info("Create table 【" + tableName + "】 SQL:" + sb.toString());
        return sb.toString();
    }

    /**
     * 增加字段语句
     */
    public static String getAddColumnSQL(DBType type, String tableName, String columnName, ValueType colType, boolean nullable, Integer length) {
        return getAddColumnSQL(type, null, tableName, columnName, colType, nullable, length);
    }

    /**
     * 增加字段语句,标准
     * @param type
     * @param schema
     * @param tableName
     * @param columnName
     * @param colType
     * @param nullable
     * @param length
     * @return
     */
    public static String getAddColumnSQL(DBType type, String schema, String tableName, String columnName, ValueType colType, boolean nullable, Integer length) {
        StringBuilder sb = new StringBuilder();
        if (StringUtils.isEmpty(schema)) {
            sb.append("alter table ").append(tableName.toLowerCase()).append(' ');
        } else {
            sb.append("alter table ").append(schema).append('.').append(tableName.toLowerCase()).append(' ');
        }
        sb.append("add ").append(columnName).append(' ').append(getColumnTypeSQL(type, colType, length));
        sb.append(nullable ? " " : " not null");
        logger.info("SQL:" + sb.toString());
        return sb.toString();
    }

    /**
     * 删除字段语句
     * @return
     */
    public static String getDropColumnSQL(DBType type, String tableName, String columnName){
        return getDropColumnSQL(type, null, tableName, columnName);
    }

    /**
     * 删除字段语句,标准
     * @param type
     * @param tableName
     * @param columnName
     * @return
     */
    public static String getDropColumnSQL(DBType type, String schema, String tableName, String columnName){
        StringBuilder sb= new StringBuilder();
        if (StringUtils.isEmpty(schema)){
            sb.append("alter table ").append(tableName.toLowerCase()).append(' ');
        }else{
            sb.append("alter table ").append(schema).append('.').append(tableName.toLowerCase()).append(' ');
        }
        switch (type) {
            case MYSQL:
            case SQLSERVER:
                sb.append("drop column ").append(columnName.toLowerCase());
                break;
            case ORACLE:
                sb.append("drop (").append(columnName.toLowerCase()).append(')');
                break;
            default:
                break;
        }
        logger.info("SQL:" + sb.toString());
        return sb.toString();
    }

    /**
     * 修改字段长度
     */
    public static String getAlertColumnLengthSQL(DBType type, String tableName, String columnName, ValueType colType, Integer newlength){
        return getAlertColumnLengthSQL(type, null, tableName, columnName, colType, newlength);
    }

    /**
     * 修改字段长度,标准
     * @param type
     * @param schema
     * @param tableName
     * @param columnName
     * @param newlen
     * @return
     */
    public static String getAlertColumnLengthSQL(DBType type, String schema, String tableName, String columnName, ValueType colType, Integer newlen) {
        if(!(ValueType.STRING_VARCHAR == colType || ValueType.STRING_CHAR == colType)){
            return null;
        }
        StringBuilder sb = new StringBuilder();
        if (StringUtils.isEmpty(schema)){
            sb.append("alter table ").append(tableName.toLowerCase()).append(' ');
        }else{
            sb.append("alter table ").append(schema).append('.').append(tableName.toLowerCase()).append(' ');
        }
        switch (type) {
            case SQLSERVER:
                sb.append("alter column ").append(columnName.toLowerCase()).append(" ");
                sb.append(getColumnTypeSQL(type, colType));
                sb.append("(").append(newlen).append(")");
                break;
            default:
                sb.append("modify ").append(columnName.toLowerCase()).append(" ");
                sb.append(getColumnTypeSQL(type, colType));
                sb.append("(").append(newlen).append(")");
                break;
        }
        logger.info("SQL:" + sb.toString());
        return sb.toString();
    }

    /**
     * 删除表字段主键关系
     * @param type
     * @param schema
     * @param tableName
     * @return
     */
    public static String getDropPrimaryKeySQL(DBType type, String schema, String tableName) {
        StringBuilder sb = new StringBuilder();
        if (StringUtils.isEmpty(schema)) {
            sb.append("alter table ").append(tableName.toLowerCase()).append(" drop primary key");
        } else {
            sb.append("alter table ").append(schema).append('.').append(tableName).append(" drop primary key");
        }
        logger.info("SQL:" + sb.toString());
        return sb.toString();
    }

    /**
     * 添加表字段主键关系
     * @param type
     * @param schema
     * @param tableName
     * @param columnNames
     * @return
     */
    public static String getAddPrimaryKeySQL(DBType type, String schema, String tableName, String[] columnNames) {
        StringBuilder sb= new StringBuilder();
        if (StringUtils.isEmpty(schema)){
            sb.append("alter table ").append(tableName.toLowerCase()).append(' ');
        }else{
            sb.append("alter table ").append(schema).append('.').append(tableName.toLowerCase()).append(' ');
        }
        sb.append("add constraint ");
        sb.append(tableName.toLowerCase()).append("_pk").append(" primary key (");
        for(int i= 0; i < columnNames.length; i++) {
            if (i != 0) {
                sb.append(", ");
            }
            sb.append(columnNames[i].toLowerCase());
        }
        sb.append(")");
        logger.info("SQL:" + sb.toString());
        return sb.toString();
    }

    /**
     * 增加对表的注释
     * @param type          数据库类型
     * @param tableName     表名
     * @param tableAlias    表注释
     * @return
     */
    public static String getAddTableCommentSQL(DBType type, String tableName, String tableAlias){
        StringBuffer sb = new StringBuffer();
        switch (type) {
            case MYSQL:
                sb.append("alter table " + tableName + " comment '" + tableAlias + "'" );
                break;
            case POSTGRESQL:
            case ORACLE:
                sb.append("COMMENT ON TABLE " + tableName + " is  '" + tableAlias + "'");
                break;
            case SQLSERVER:
            case DEFAULT:
            default:
                break;
        }
        logger.info("Comment table 【" + tableName + "】 SQL:" + sb.toString());
        return sb.toString();
    }

    public static List<String> getAddColumnCommentSQL(DBType type, String tableName, String[] columnNames, String[] columnAlias, ValueType[] valueTypes, boolean[] nullAbles, Integer[] fieldLens, String[] identityKeys) {
        List<String> columnNameList = new ArrayList(Arrays.asList(columnNames));
        List<String> columnAliasList = new ArrayList(Arrays.asList(columnAlias));
        List<ValueType> valueTypesList = new ArrayList(Arrays.asList(valueTypes));
        List<String> identityKeyList = new ArrayList(Arrays.asList(identityKeys));
        return getAddColumnCommentSQL(type, tableName, columnNameList, columnAliasList, valueTypesList, nullAbles, fieldLens, identityKeyList);
    }

    /**
     * 增加对表字段的描述
     * @param type
     * @param tableName
     * @param columnNames
     * @param columnAlias
     * @param valueTypes
     * @param nullables
     * @param fieldLens
     * @param identityKeys
     * @return
     */
    public static List<String> getAddColumnCommentSQL(DBType type, String tableName, List<String> columnNames, List<String> columnAlias, List<ValueType> valueTypes, boolean[] nullables, Integer[] fieldLens, List<String> identityKeys){
        List<String> ret = new ArrayList();
        if (columnAlias != null && columnNames != null && valueTypes != null) {
            if (columnAlias.size() == columnNames.size() && columnNames.size() == valueTypes.size()) {
                for(int i = 0, len = columnAlias.size(); i < len; ++i) {
                    StringBuffer sb = new StringBuffer();
                    if (!((String) columnAlias.get(i)).equalsIgnoreCase((String) columnNames.get(i))) {
                        switch (type) {
                            case MYSQL:
                                String columnName = columnNames.get(i);
                                sb.append("alter table " + tableName + " modify column " + columnName + " ");
                                sb.append(getColumnTypeSQL(type, (ValueType) valueTypes.get(i), fieldLens[i]));
                                if (identityKeys.contains(columnName)) {
                                    sb.append(" auto_increment ");
                                }
                                sb.append(" COMMENT '" + (String) columnAlias.get(i) + "'");
                                ret.add(sb.toString());
                                break;
                            case POSTGRESQL:
                            case ORACLE:
                                sb.append("COMMENT ON COLUMN " + tableName + "." + (String)columnNames.get(i) + " is '" + (String) columnAlias.get(i) + "'");
                                ret.add(sb.toString());
                                break;
                            case SQLSERVER:
                            case DEFAULT:
                            default:
                                break;
                        }
                    }
                    logger.info("Comment column 【" + tableName + "." + columnNames.get(i) +  "】 SQL:" + sb.toString());
                }
                return ret;
            } else {
                logger.error(tableName + " || columnAlias[" + columnAlias.size() + "] or columnNames[" + columnNames.size() + "] or valueTypes[" + valueTypes.size() + "]'length is not equal.");
                return null;
            }
        } else {
            logger.error(tableName + " || columnAlias or columnNames or valueTypes is null.");
            return null;
        }
    }

    /**
     * 增加索引
     * @param type      数据库类型
     * @param tableName 表名称
     * @param indexName 索引名称
     * @param columnNames   字段名称's
     * @return
     */
    public static String getAddIndexSQL(DBType type, String tableName, String indexName, String[] columnNames){
        if(0 == columnNames.length || null == columnNames)
            return null;
        StringBuilder sb = new StringBuilder();
        sb.append("create index ");
        sb.append(indexName);
        sb.append(" on ");
        sb.append(tableName);
        sb.append("(");
        for (String column : columnNames) {
            sb.append(column);
            sb.append(",");
        }
        sb.deleteCharAt(sb.length()-1);
        sb.append(")");
        logger.info("SQL:" + sb.toString());
        return sb.toString();
    }

    /**
     * 获取字段能否为空
     * @param nullable      能否为空
     * @return
     */
    private static String getColumnNullableSQL(boolean nullable) {
        return nullable ? "" : "not null";
    }

    /**
     * 获取ValueType在各数据库中的字段类型
     * @param dbType		数据库类型
     * @param valType       字段类型
     * @return
     */
    private static String getColumnTypeSQL(DBType dbType, ValueType valType) {
        if (null == columnTypeMapCache)
            initColumnTypeMapCache();
        Map<ValueType, String> map= columnTypeMapCache.get(dbType);
        if (map == null)
            logger.error("ColumnType Map is Null.");
        return map.get(valType);
    }

    /**
     * 获取ValueType在各数据库中的字段类型
     * @param dbType		数据库类型
     * @param valType       字段类型
     * @param fieldLength	字段长度
     * @return
     */
    private static String getColumnTypeSQL(DBType dbType, ValueType valType, Integer fieldLength) {
        if (columnTypeMapCache == null)
            initColumnTypeMapCache();
        Map<ValueType, String> map = columnTypeMapCache.get(dbType);
        if (map == null)
            logger.error("ColumnType Map is Null.");
        String columnType = map.get(valType);
        if(null != fieldLength){
            if (valType == ValueType.STRING_CHAR || valType == ValueType.STRING_VARCHAR ) {
                columnType = columnType + "(" + fieldLength + ")";
            }
        }
        return columnType;
    }

    /**
     * 初始化各数据库的字段类型Map集合
     */
    private static void initColumnTypeMapCache() {
        columnTypeMapCache = new HashMap<>();

        Map<ValueType, String> oracleMap= new HashMap<ValueType, String>();
        oracleMap.put(ValueType.INT, "number(10,0)");
        oracleMap.put(ValueType.INTEGER, "number(10,0)");
        oracleMap.put(ValueType.TINYINT, "number(3,0)");
        oracleMap.put(ValueType.LONG, "number");
        oracleMap.put(ValueType.SHORT, "number");
        oracleMap.put(ValueType.FLOAT, "number");
        oracleMap.put(ValueType.DOUBLE, "number");
        oracleMap.put(ValueType.BYTE, "raw(1-2000)");
        oracleMap.put(ValueType.BOOLEAN, "number(1)");
        oracleMap.put(ValueType.STRING_CHAR, "char");
        oracleMap.put(ValueType.STRING_VARCHAR, "varchar2");
        oracleMap.put(ValueType.DATE, "date");
        oracleMap.put(ValueType.DATETIME, "timestamp");
        oracleMap.put(ValueType.TIME, "date");
        oracleMap.put(ValueType.TIMESTAMP, "timestamp");
        oracleMap.put(ValueType.BIGDECIMAL, "number");
        oracleMap.put(ValueType.ASCII, "clob");
        oracleMap.put(ValueType.BINARY, "blob");
        oracleMap.put(ValueType.TEXT, "clob");
        oracleMap.put(ValueType.MONEY, "number(19,4)");
        columnTypeMapCache.put(DBType.ORACLE, oracleMap);

        Map<ValueType, String> mysqlMap= new HashMap<ValueType, String>();
        mysqlMap.put(ValueType.INT, "int");
        mysqlMap.put(ValueType.INTEGER, "int");
        mysqlMap.put(ValueType.TINYINT, "tinyint");
        mysqlMap.put(ValueType.LONG, "bigint");
        mysqlMap.put(ValueType.SHORT, "smallint");
        mysqlMap.put(ValueType.FLOAT, "float");
        mysqlMap.put(ValueType.DOUBLE, "double");
        mysqlMap.put(ValueType.BYTE, "binary");
        mysqlMap.put(ValueType.BOOLEAN, "bit");
        mysqlMap.put(ValueType.STRING_CHAR, "char");
        mysqlMap.put(ValueType.STRING_VARCHAR, "varchar");
        mysqlMap.put(ValueType.DATE, "date");
        mysqlMap.put(ValueType.DATETIME, "datetime");
        mysqlMap.put(ValueType.TIME, "time");
        mysqlMap.put(ValueType.TIMESTAMP, "timestamp");
        mysqlMap.put(ValueType.BIGDECIMAL, "decimal");
        mysqlMap.put(ValueType.ASCII, "longtext");
        mysqlMap.put(ValueType.BINARY, "longblob");
        mysqlMap.put(ValueType.TEXT, "text");
        mysqlMap.put(ValueType.MONEY, "double");
        columnTypeMapCache.put(DBType.MYSQL, mysqlMap);

        Map<ValueType, String> sqlServerMap= new HashMap<ValueType, String>();
        sqlServerMap.put(ValueType.INT, "int");
        sqlServerMap.put(ValueType.INTEGER, "int");
        sqlServerMap.put(ValueType.TINYINT, "tinyint");
        sqlServerMap.put(ValueType.LONG, "bigint");
        sqlServerMap.put(ValueType.SHORT, "smallint");
        sqlServerMap.put(ValueType.FLOAT, "real");
        sqlServerMap.put(ValueType.DOUBLE, "float");
        sqlServerMap.put(ValueType.BYTE, "binary");
        sqlServerMap.put(ValueType.BOOLEAN, "bit");
        sqlServerMap.put(ValueType.STRING_CHAR, "char");
        sqlServerMap.put(ValueType.STRING_VARCHAR, "varchar");
        sqlServerMap.put(ValueType.DATE, "date");
        sqlServerMap.put(ValueType.DATETIME, "datetime");
        sqlServerMap.put(ValueType.TIME, "time");
        sqlServerMap.put(ValueType.TIMESTAMP, "datetime");
        sqlServerMap.put(ValueType.BIGDECIMAL, "decimal");
        sqlServerMap.put(ValueType.ASCII, "text");
        sqlServerMap.put(ValueType.BINARY, "text");
        sqlServerMap.put(ValueType.TEXT, "text");
        sqlServerMap.put(ValueType.MONEY, "money");
        columnTypeMapCache.put(DBType.SQLSERVER, sqlServerMap);

        Map<ValueType, String> postgreSQLMap= new HashMap<ValueType, String>();
        postgreSQLMap.put(ValueType.INT, "integer");
        postgreSQLMap.put(ValueType.INTEGER, "integer");
        postgreSQLMap.put(ValueType.TINYINT, "tinyint");
        postgreSQLMap.put(ValueType.LONG, "bigint");
        postgreSQLMap.put(ValueType.SHORT, "smallint");
        postgreSQLMap.put(ValueType.FLOAT, "real");
        postgreSQLMap.put(ValueType.DOUBLE, "double");
        postgreSQLMap.put(ValueType.BYTE, "bytea");
        postgreSQLMap.put(ValueType.BOOLEAN, "boolean");
        postgreSQLMap.put(ValueType.STRING_CHAR, "char");
        postgreSQLMap.put(ValueType.STRING_VARCHAR, "varchar");
        postgreSQLMap.put(ValueType.DATE, "date");
        postgreSQLMap.put(ValueType.DATETIME, "timestamp");
        postgreSQLMap.put(ValueType.TIME, "timestamp");
        postgreSQLMap.put(ValueType.TIMESTAMP, "timestamp");
        postgreSQLMap.put(ValueType.BIGDECIMAL, "decimal");
        postgreSQLMap.put(ValueType.ASCII, "text");
        postgreSQLMap.put(ValueType.BINARY, "text");
        postgreSQLMap.put(ValueType.TEXT, "text");
        sqlServerMap.put(ValueType.MONEY, "numeric");
        columnTypeMapCache.put(DBType.SQLSERVER, postgreSQLMap);
    }

    public static boolean checkExist(Connection conn, String sql) {
        boolean flag;
        Statement sm = null;
        try {
            sm = conn.createStatement();
            sm.executeQuery(sql);
            flag = true;
        } catch (Exception e) {
            flag = false;
        } finally{
            try {
                if(null != sm){
                    sm.close();
                }
            } catch (Exception e) { }
        }
        return flag;
    }
}
